--通过作业调度 进行人员转正、工龄增加 提醒

CREATE PROCEDURE dbo.peopleWarn
AS
SET NOCOUNT ON
DECLARE @id BIGINT
DECLARE @peopleName VARCHAR(100)
DECLARE @joinData VARCHAR(100)
DECLARE @zzrq VARCHAR(100)
DECLARE @nowData VARCHAR(100)
	begin 
		set @nowData=SUBSTRING(CONVERT(varchar(100), getdate(), 20) ,0,11);
	end;
DECLARE Roy_cur CURSOR FOR 
	select id'id',
		   people_name'peopleName',
		   SUBSTRING(CONVERT(varchar(100), join_date, 20) ,0,11) 'joinData',
		   SUBSTRING(CONVERT(varchar(100), zzrq, 20) ,0,11) 'zzrq'
	from dbo.T_People_Info
	where state!=3
OPEN Roy_cur
FETCH NEXT FROM Roy_cur INTO @id,@peopleName,@joinData,@zzrq

WHILE @@FETCH_STATUS=0
BEGIN
	PRINT @@FETCH_STATUS
	 IF (SUBSTRING(@nowData ,6,11) = SUBSTRING(@joinData ,6,11))--添加工龄提醒
			begin 
			 INSERT INTO dbo.T_SysMsg_Info (Msg_Title,Msg_Content,Receive_User,unWarn_User,Business_Type,Version,Invalid_Flag,Delete_Flag,Check_Flag,Create_Date) 
			 VALUES ('工龄提醒',@peopleName+'的工龄增加了!','1,','0,','13',0,0,0,0,getdate());
			end;
	 IF ( @zzrq='' and datediff(day,@joinData,@nowData)>50 and datediff(day,@joinData,@nowData)<62)--添加转正提醒
			begin 
			 INSERT INTO dbo.T_SysMsg_Info (Msg_Title,Msg_Content,Receive_User,unWarn_User,Business_Type,Version,Invalid_Flag,Delete_Flag,Check_Flag,Create_Date) 
			 VALUES ('转正提醒',@peopleName+'入职已满两个月!','1,','0,','14',0,0,0,0,getdate());
			end;
	FETCH NEXT FROM Roy_cur INTO @id,@peopleName,@joinData,@zzrq
END
CLOSE Roy_cur 
DEALLOCATE Roy_Cur
GO




